﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
using Utility;
namespace Service
{
    /// <summary>
    /// 功能：Menu类数据访问
    /// 创建人：Wilson
    /// 创建时间：2013-1-30   
    /// </summary>
    public class MenuService : IDisposable
    {
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~MenuService()
        {
            Dispose(false);
        }

        #endregion

        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
		#region##MapperMenu
        public IList<Menu> MapperList(SqlDataReader reader)
         {
            IList<Menu> list = new List<Menu>();
            Menu obj=null;
            while (reader.Read())
            {
                obj = new Menu();
                obj.MenuId = (int)reader["MenuId"];
                obj.MenuName = (string)reader["MenuName"];
                obj.Url = reader["Url"] as string;
                obj.ImageUrl = reader["ImageUrl"] as string;
                obj.PMenuId = reader["PMenuId"] as int?;
                obj.Title = reader["Title"] as string;
                obj.IsModule = reader["IsModule"] as bool?;
                obj.Sort = reader["Sort"] as int?;
                list.Add(obj);
            }
            return list;
         }
        public Menu Mapper(SqlDataReader reader)
         {
            Menu obj=null;
            if (reader.Read())
            {
                obj = new Menu();
                obj.MenuId = (int)reader["MenuId"];
                obj.MenuName = (string)reader["MenuName"];
                obj.Url = reader["Url"] as string;
                obj.ImageUrl = reader["ImageUrl"] as string;
                    obj.PMenuId = reader["PMenuId"] as int?;
                obj.Title = reader["Title"] as string;
                    obj.IsModule = reader["IsModule"] as bool?;
                    obj.Sort = reader["Sort"] as int?;
            }
            return obj;
         }
        #endregion
        #region##通过主键ID得到Menu
        /// <summary>
        /// 功能：通过ID得到Menu
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        public Menu GetById(int menuid)
        {            
            string sql = "select * from Menu where MenuId = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",4)
            };
            
            SqlDataReader reader=SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql,sp);
            return Mapper(reader);
        }
        #endregion
		
		#region##通过主键ID删除Menu
        /// <summary>
        /// 功能：通过主键ID删除Menu
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(int menuid)
		{
			int i = 0;
			string sql = "delete from Menu where MenuId = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",4)
            };
            i= SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
			return i;
		}
        /// <summary>
        /// 功能：通过主键ID删除Menu
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
		public int DeleteById(SqlTransaction transaction,int menuid)
		{
			int i = 0;
			string sql = "delete from Menu where MenuId = @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",4)
            };
            i= SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
			return i;
		}
		#endregion			
		
        #region##添加Menu
        /// <summary>
        /// 功能：添加Menu
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(Menu menu)
        {
            int result = 0;
            string sql = "insert into Menu(MenuId,MenuName,Url,ImageUrl,PMenuId,Title,IsModule,Sort) values(@MenuId,@MenuName,@Url,@ImageUrl,@PMenuId,@Title,@IsModule,@Sort)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@MenuId",menu.MenuId),
				new SqlParameter("@MenuName",menu.MenuName),
				new SqlParameter("@Url",menu.Url),
				new SqlParameter("@ImageUrl",menu.ImageUrl),
				new SqlParameter("@PMenuId",menu.PMenuId),
				new SqlParameter("@Title",menu.Title),
				new SqlParameter("@IsModule",menu.IsModule),
				new SqlParameter("@Sort",menu.Sort)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：添加Menu
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int Add(SqlTransaction transaction,Menu menu)
        {
            int result = 0;
            string sql = "insert into Menu(MenuId,MenuName,Url,ImageUrl,PMenuId,Title,IsModule,Sort) values(@MenuId,@MenuName,@Url,@ImageUrl,@PMenuId,@Title,@IsModule,@Sort)";			
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@MenuId",menu.MenuId),
				new SqlParameter("@MenuName",menu.MenuName),
				new SqlParameter("@Url",menu.Url),
				new SqlParameter("@ImageUrl",menu.ImageUrl),
				new SqlParameter("@PMenuId",menu.PMenuId),
				new SqlParameter("@Title",menu.Title),
				new SqlParameter("@IsModule",menu.IsModule),
				new SqlParameter("@Sort",menu.Sort)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##得到Menu集合
		/// <summary>
        /// 功能：得到Menu集合
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>       
        /// <returns></returns>
		public IList<Menu> GetList()
		{
			string sql = "select * from Menu";			
			SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString,CommandType.Text,sql);
            return MapperList(reader);
		}
		#endregion
		
		#region##修改Menu
        /// <summary>
        /// 功能：修改Menu
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModMenu(Menu menu)
        {
            int result = 0;
            string sql = "update Menu set MenuId = @MenuId,MenuName = @MenuName,Url = @Url,ImageUrl = @ImageUrl,PMenuId = @PMenuId,Title = @Title,IsModule = @IsModule,Sort = @Sort where MenuId = @MenuId";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@MenuId",menu.MenuId),
				new SqlParameter("@MenuName",menu.MenuName),
				new SqlParameter("@Url",menu.Url),
				new SqlParameter("@ImageUrl",menu.ImageUrl),
				new SqlParameter("@PMenuId",menu.PMenuId),
				new SqlParameter("@Title",menu.Title),
				new SqlParameter("@IsModule",menu.IsModule),
				new SqlParameter("@Sort",menu.Sort)
            };
            result = SqlHelper.ExecuteNonQuery(WebConn.connString,CommandType.Text,sql,sp);
            return result;
        }
         /// <summary>
        /// 功能：修改Menu
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int ModMenu(SqlTransaction transaction,Menu menu)
        {
            int result = 0;
            string sql = "update Menu set MenuId = @MenuId,MenuName = @MenuName,Url = @Url,ImageUrl = @ImageUrl,PMenuId = @PMenuId,Title = @Title,IsModule = @IsModule,Sort = @Sort where MenuId = @MenuId";
            SqlParameter[] sp = new SqlParameter[]
            {       
				new SqlParameter("@MenuId",menu.MenuId),
				new SqlParameter("@MenuName",menu.MenuName),
				new SqlParameter("@Url",menu.Url),
				new SqlParameter("@ImageUrl",menu.ImageUrl),
				new SqlParameter("@PMenuId",menu.PMenuId),
				new SqlParameter("@Title",menu.Title),
				new SqlParameter("@IsModule",menu.IsModule),
				new SqlParameter("@Sort",menu.Sort)
            };
            result = SqlHelper.ExecuteNonQuery(transaction,CommandType.Text,sql,sp);
            return result;
        }
        #endregion
		
		#region##统计Menu
		/// <summary>
        /// 修改Menu
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
		public int SelectCount()
		{
			int i = 0;
            string sql = "select count(*) from Menu";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
		}
		/// <summary>
        /// 修改Menu
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public int SelectCountById()
        {
            int i=0;
            string sql="select count(MenuId) from Menu";
            i = (int)SqlHelper.ExecuteScalar(WebConn.connString,CommandType.Text,sql);
            return i;
        }
		#endregion
        #region##自定义Menu
        public IList<Menu> NameFilter(string name)
        {
            IList<Menu> list = new List<Menu>();
            Menu obj = null;
            string sql = "select * from Menu where MenuName<>@name";
            SqlParameter[] sp = new SqlParameter[]
            {
				new SqlParameter("@name",name),
            };
            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql,sp);
            while (reader.Read())
            {
                obj = new Menu();
                obj.MenuId = reader["MenuId"] is DBNull?0:Convert.ToInt32(reader["MenuId"]);
                obj.MenuName = reader["MenuName"] is DBNull ? "" : reader["MenuName"].ToString();
                obj.Url = (reader["Url"] is DBNull?"":reader["url"].ToString());
                obj.ImageUrl = (reader["ImageUrl"] is DBNull?"":reader["ImageUrl"].ToString());
                obj.PMenuId = reader["PMenuId"] is DBNull ? 0 : Convert.ToInt32(reader["PMenuId"]);
                obj.Title = (reader["Title"] is DBNull?"":reader["Title"].ToString());
                //obj.IsModule = (bool)reader["IsModule"];
                obj.Sort = reader["Sort"] is DBNull?100:Convert.ToInt32(reader["Sort"]);
                list.Add(obj);
            }
            return list;
        }
        #endregion
                
    }
}
